import pymysql
import requests
from concurrent.futures import ThreadPoolExecutor, as_completed
import logging
from datetime import datetime
import re
from queue import Queue
from dbutils.pooled_db import PooledDB
from urllib.parse import unquote

# 配置项
CONFIG = {
    'DB_POOL': {
        'maxconnections': 20,
        'mincached': 5,
        'maxcached': 10,
        'maxshared': 10
    },
    'THREAD_POOL': {
        'workers': 10
    }
}

# 日志配置
log_file = f"image_sync_{datetime.now().strftime('%Y%m%d_%H%M%S')}.log"
logging.basicConfig(
    filename=log_file,
    level=logging.INFO,  # 改为 INFO 级别以查看更多日志
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# 数据库配置
source_db_config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "123456",
    "database": "zazhi2007",
    "autocommit": True,
}

target_db_config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "123456",
    "database": "5i40",
    "autocommit": True,
}

# 创建连接池
source_pool = PooledDB(creator=pymysql, **source_db_config, **CONFIG['DB_POOL'])
target_pool = PooledDB(creator=pymysql, **target_db_config, **CONFIG['DB_POOL'])

def check_image_url(url):
    """检查图片URL是否可访问"""
    try:
        if not url:
            return False
        if not url.startswith(('http://', 'https://')):
            return False
        response = requests.head(url, timeout=5, allow_redirects=True)
        return response.status_code == 200
    except:
        return False


def process_image_path(path):
    """处理图片路径"""
    if not path:
        return ""
    
    # 如果已经是完整URL，直接返回
    if path.startswith(('http://', 'https://')):
        return path
    
    # 移除开头的斜杠并拼接域名
    path = path.lstrip('/')
    return f"https://zimg.zazhiejia.com/{path}"

def get_gallery_images(goods_id, source_cursor):
    """获取商品相册图片"""
    try:
        source_cursor.execute("""
            SELECT thumb_url, img_url, img_original 
            FROM ecs_goods_gallery 
            WHERE goods_id = %s 
            ORDER BY img_sort ASC, img_id ASC
        """, (goods_id,))
        return source_cursor.fetchall()
    except Exception as e:
        logging.error(f"获取相册图片失败, goods_id: {goods_id}, 错误: {str(e)}")
        return []

def get_invalid_image_goods():
    """获取图片无效的商品"""
    target_conn = None
    try:
        target_conn = target_pool.connection()
        cursor = target_conn.cursor(pymysql.cursors.DictCursor)
        
        # 查询图片为空或无法访问的商品
        cursor.execute("""
            SELECT id, title, image, images 
            FROM fa_shop_goods 
            WHERE image = '' OR images = '' 
            OR image IS NULL OR images IS NULL
        """)
        
        invalid_goods = []
        for goods in cursor.fetchall():
            # 检查现有图片是否可访问
            image_valid = check_image_url(goods['image'])
            images_valid = any(check_image_url(img) for img in goods['images'].split(',') if img)
            
            if not image_valid or not images_valid:
                invalid_goods.append(goods)
                print(f"发现无效图片商品: {goods['title']}")
        
        return invalid_goods
    finally:
        if target_conn:
            target_conn.close()
            

def get_source_goods_images(goods_title, source_cursor):
    """从源数据库获取商品图片"""
    # 通过商品名称查询源数据库
    source_cursor.execute("""
        SELECT goods_id, goods_thumb, goods_img, original_img 
        FROM ecs_goods 
        WHERE goods_name = %s
    """, (goods_title,))
    goods = source_cursor.fetchone()
    
    if not goods:
        return None, []
    
    # 获取相册图片
    source_cursor.execute("""
        SELECT thumb_url, img_url, img_original 
        FROM ecs_goods_gallery 
        WHERE goods_id = %s 
        ORDER BY img_sort ASC, img_id ASC
    """, (goods['goods_id'],))
    gallery = source_cursor.fetchall()
    
    return goods, gallery

def process_single_goods(goods):
    """处理单个商品的图片"""
    source_conn = None
    target_conn = None
    try:
        source_conn = source_pool.connection()
        target_conn = target_pool.connection()
        source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
        target_cursor = target_conn.cursor()

        goods_title = goods['title']
        
        # 从源数据库获取商品图片
        source_goods, gallery = get_source_goods_images(goods_title, source_cursor)
        if not source_goods:
            print(f"未找到对应商品: {goods_title}")
            return

        # 处理主图
        main_image = None
        images = []
        
        # 尝试使用商品主图
        if source_goods['goods_thumb']:
            main_image = process_image_path(source_goods['goods_thumb'])
            if not check_image_url(main_image):
                main_image = None
        
        # 尝试使用商品大图
        if not main_image and source_goods['goods_img']:
            main_image = process_image_path(source_goods['goods_img'])
            if not check_image_url(main_image):
                main_image = None
        
        # 处理相册图片
        if gallery:
            for img in gallery:
                # 如果还没有主图，使用第一张相册缩略图
                if not main_image and img['thumb_url']:
                    main_image = process_image_path(img['thumb_url'])
                    if not check_image_url(main_image):
                        main_image = None
                
                # 添加相册图片
                if img['img_url']:
                    img_url = process_image_path(img['img_url'])
                    if check_image_url(img_url):
                        images.append(img_url)

        # 如果找到有效图片，更新数据库
        if main_image:
            images_str = ','.join(images) if images else main_image
            
            # 更新商品图片
            target_cursor.execute("""
                UPDATE fa_shop_goods 
                SET image = %s, images = %s 
                WHERE title = %s
            """, (main_image, images_str, goods_title))
            
            target_conn.commit()
            print(f"已更新商品图片: {goods_title}")
        else:
            print(f"未找到有效图片: {goods_title}")
            
    except Exception as e:
        logging.error(f"处理商品 {goods_title} 时发生错误: {str(e)}")
        print(f"处理商品 {goods_title} 时发生错误: {str(e)}")
    finally:
        if source_conn:
            source_conn.close()
        if target_conn:
            target_conn.close()

def main():
    """主函数"""
    try:
        print("开始同步商品图片...")
        
        # 获取需要处理的商品列表
        invalid_goods = get_invalid_image_goods()
        total_goods = len(invalid_goods)
        
        if not total_goods:
            print("没有需要处理的商品")
            return
            
        print(f"共有 {total_goods} 个商品需要处理")

        # 使用线程池处理
        with ThreadPoolExecutor(max_workers=CONFIG['THREAD_POOL']['workers']) as executor:
            futures = [executor.submit(process_single_goods, goods) for goods in invalid_goods]
            
            # 等待所有任务完成
            for i, future in enumerate(as_completed(futures), 1):
                try:
                    future.result()
                    if i % 10 == 0:
                        print(f"进度: {i}/{total_goods}")
                except Exception as e:
                    logging.error(f"任务执行失败: {str(e)}")

        print("商品图片同步完成！")
        
    except Exception as e:
        logging.error(f"同步过程中发生错误: {str(e)}")
        print(f"同步过程中发生错误: {str(e)}")

if __name__ == "__main__":
    main()            


if __name__ == "__main__":
    main()
